package com.codeoss.framework.db.service.impl;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.codeoss.framework.data.metadata.Column;
import com.codeoss.framework.data.metadata.Table;
import com.codeoss.framework.util.DBUtil;
import com.codeoss.framework.util.StringUtils;

@Service
public class OracleDDLService extends AbstractDDLService {
	@Autowired
	private DBUtil dbUtil;
	public void createTable(Connection connection, Table table) {
		Statement stmt = null;
		String sql = null;
		try {
			stmt = connection.createStatement();
			sql = getCreateTableSql(table);
			stmt.execute(sql);
			
			String sequenceSql=getCreateSequenceSql(table);
			stmt.execute(sequenceSql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			throw new RuntimeException(sql, e);
		} finally {
			dbUtil.closeStatement(stmt);
		}
	}
	
	@Override
	public String getCreateTableSql(Table table) {
		// TODO Auto-generated method stub
		StringBuilder sb=new StringBuilder();
		sb.append("create table ").append(table.getTableName()).append("(");
		List<Column> columnList=table.getAllColumnList();
		for(int i=0;i<columnList.size();i++) {
			if(i>0) {
				sb.append(",");
			}
			Column column=columnList.get(i);
			sb.append(column.getColumnName());
			if(column.isPrimaryKey()) {
				sb.append(" number(19,0) NOT NULL PRIMARY KEY ");
				continue;
			}
			String type=column.getDataType();
			if("string".equals(type)) {
				String sqlType=column.getSqlType();
				if(!StringUtils.isEmpty(sqlType)) {
					sb.append(" ");
					if("char".equals(sqlType)) {
						sb.append("char(");
						if(column.getLength()==0) {
						    sb.append(10);
						}else {
							sb.append(column.getLength());
						}
						sb.append(")");
					}else if("text".equals(sqlType)){
						sb.append(" clob ");
					}
				}else {
					sb.append(" varchar2(");
					if(column.getLength()==0) {
						sb.append(50);
					}else {
						sb.append(column.getLength());
					}
					sb.append(")");
				}
			}else if("integer".equals(type)) {
				sb.append(" int");
			}else if("long".equals(type)) {
				sb.append(" number(19,0)");
			}else if("bigdecimal".equals(type)) {
				sb.append(" number(");
				if(column.getLength()==0) {
					sb.append(20);
				}else {
					sb.append(column.getLength());
				}
				sb.append(",");
				if(column.getPrecision()==0) {
					sb.append(8);
				}else {
					sb.append(column.getPrecision());
				}
				sb.append(")");
			}else if("boolean".equals(type)) {
				sb.append(" char(1)");
			}else if("datetime".equals(type)) {
				sb.append(" timestamp");
			}else if("date".equals(type)) {
				sb.append(" date");
			}else if("time".equals(type)) {
				sb.append(" date");
			}else if("binary".equals(type)) {
				sb.append(" blob ");
			}
			if(!column.isNullable()) {
				sb.append(" not null");
			}
		}
		sb.append(")");
		return sb.toString();
	}

	protected String getCreateSequenceSql(Table table) {
		StringBuilder sb=new StringBuilder();
		sb.append("CREATE SEQUENCE ").append(table.getTableName().replace("_","")).append(" INCREMENT BY 1 START WITH 1  MINVALUE 1 NOCYCLE NOORDER");
		return sb.toString();
	}
	
	@Override
	public String getCreateColumnSql(Table table, Column column) {
		// TODO Auto-generated method stub
		StringBuilder sb = new StringBuilder();
		sb.append(" ALTER TABLE ").append(table.getTableName()).append(" ADD ").append(column.getColumnName());
		sb.append(" ");
		String type = column.getDataType();
		if ("string".equals(type)) {
			String sqlType = column.getSqlType();
			if (!StringUtils.isEmpty(sqlType)) {
				sb.append(" ");
				if ("char".equals(sqlType)) {
					sb.append("char(");
					if (column.getLength() == 0) {
						sb.append(10);
					} else {
						sb.append(column.getLength());
					}
					sb.append(")");
				}else if("text".equals(sqlType)){
					sb.append(" clob ");
				}
			} else {
				sb.append(" varchar2(");
				if (column.getLength() == 0) {
					sb.append(50);
				} else {
					sb.append(column.getLength());
				}
				sb.append(")");
			}
		} else if ("integer".equals(type)) {
			sb.append(" int");
		} else if ("long".equals(type)) {
			sb.append(" number(19,0)");
		} else if ("bigdecimal".equals(type)) {
			sb.append(" number(");
			if (column.getLength() == 0) {
				sb.append(20);
			} else {
				sb.append(column.getLength());
			}
			sb.append(",");
			if (column.getPrecision() == 0) {
				sb.append(8);
			} else {
				sb.append(column.getPrecision());
			}
			sb.append(")");
		} else if ("boolean".equals(type)) {
			sb.append(" char(1)");
		} else if ("datetime".equals(type)) {
			sb.append(" timestamp");
		} else if ("date".equals(type)) {
			sb.append(" date");
		} else if ("time".equals(type)) {
			sb.append(" date ");
		} else if ("binary".equals(type)) {
			sb.append(" blob ");
		}
		if (!column.isNullable()) {
			sb.append(" not null");
		}
		sb.append(";");
		return sb.toString();
	}

	@Override
	public String getCreateUniqueIndexSql(Table table, Column column) {
		// TODO Auto-generated method stub
		if(column.isPrimaryKey()) {
			return null;
		}
		StringBuilder sql = new StringBuilder();
		sql.append("CREATE UNIQUE INDEX ");
		String name="idx_" + table.getTableName() + "_" + column.getColumnName();
		name=name.replace("workflow_","");
		name=name.replace("product","p");
		name=name.replace("attribute","attr");
		name=name.replace("_","");
		if(name.length()>30) {
			name=name.substring(0, 30);
		}
		sql.append(name).append(" on ").append(table.getTableName());
		sql.append("(").append(column.getColumnName()).append(")");
		return sql.toString();
	}

	@Override
	public String getCreateForeignKeySql(Table table, Column column) {
		// TODO Auto-generated method stub
		if(column.isPrimaryKey()) {
			return null;
		}
		StringBuilder sql = new StringBuilder();
		sql.append("ALTER TABLE ").append(table.getTableName()).append(" ADD CONSTRAINT ");
		String name="fk_" + table.getTableName() + "_" + column.getColumnName();
		name=name.replace("workflow_","");
		name=name.replace("product","p");
		name=name.replace("attribute","attr");
		name=name.replace("stock","s");
		name=name.replace("movement","m");
		name=name.replace("_","");
		if(name.length()>30) {
			name=name.substring(0, 30);
		}
		sql.append(name).append(" FOREIGN KEY (");
		sql.append("").append(column.getColumnName()).append(")").append(" REFERENCES ");
		sql.append(column.getLookupInfo().getDataSourceConfig().getTableName()).append("(").append("id");
		sql.append(")");
		return sql.toString();
	}

}
